package com.qinyh.util;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.qinyh.entity.Book;
import com.qinyh.entity.BookMark;
import com.qinyh.entity.Note;
import com.qinyh.param.Constant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CRDBHelper extends SQLiteOpenHelper {
	private SQLiteDatabase sql = null;
	private boolean isopen = false;

	public CRDBHelper(Context c) {
		super(c, Constant.DB_NAME, null, Constant.DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.v("debug", "start create table");
		db.execSQL(Constant.CREATE_TABLE_BOOK);
		db.execSQL(Constant.CREATE_TABLE_BOOK_MARK);
		db.execSQL(Constant.CREATE_TABLE_NOTE);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	/** save the book info to database */
	public int saveBook(Book b) {
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
		}
		String[] col = new String[] { Constant.BOOK_ID, Constant.BOOK_PATH };
		Cursor cur = sql.query(Constant.BOOK, col, Constant.BOOK_PATH + "=\""
				+ b.getId() + "\"", null, null, null, null);
		int c = cur.getCount();
		if (c == 0) {
			ContentValues values = new ContentValues();
			values.put(Constant.BOOK_PATH, b.getId());
			cur.close();
			return (int) sql.insert(Constant.BOOK, null, values);
		} else {
			cur.moveToLast();
			int i = cur.getInt(0);
			cur.close();
			return i;
		}

	}

	/**
	 * 保存书签到数据库中
	 * 
	 * @param bm
	 *            要保存的书签
	 * @return 此书签在数据库中的ID
	 */
	public boolean addBookMark(BookMark bm) {
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
		}
		ContentValues values = new ContentValues();
		values.put(Constant.BOOK_ID, bm.getBookId());
		values.put(Constant.BOOK_MARK_NAME, bm.getMarkName());
		values.put(Constant.BOOK_MARK_OFFSET, bm.getCurrentOffset());
		values.put(Constant.Book_MARK_SAVETIME, bm.getSaveTime());
		long x = sql.insert(Constant.BOOK_MARK_TABLE_NAME, null, values);
		Log.v("debug", "insert into chapterSeq:" + bm.getCurrentOffset());
		if (x > 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 删除书签
	 * 
	 * @param bmId
	 *            书签的ID
	 * @return 删除是否成功
	 */
	public boolean deleteBookMark(int bmId) {
		String tag = "deleteBookMark";
		Log.v("debug", "removeResult:" + bmId);
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
			Log.d(tag, "open the database...");
		}

		String s = "delete from " + Constant.BOOK_MARK_TABLE_NAME + " where "
				+ Constant.BOOK_MARK_ID + " =" + bmId;

		Log.d(tag, s);
		sql.execSQL(s);
		return true;
	}

	/**
	 * 查询某个书籍的所有书签
	 * 
	 * @param bookId
	 * @return
	 */
	public List<BookMark> queryAllBookMark(int bookId) {
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
		}
		String[] columns = new String[] { Constant.BOOK_MARK_ID,
				Constant.BOOK_MARK_NAME, Constant.BOOK_MARK_OFFSET,
				Constant.Book_MARK_SAVETIME };
		Cursor cur = sql.query(Constant.BOOK_MARK_TABLE_NAME, columns,
				Constant.BOOK_ID + "=\"" + bookId + "\"", null, null, null,
				Constant.BOOK_MARK_ID + " desc");
		Log.v("debug", "rowCount:" + cur.getCount());
		List<BookMark> list = new ArrayList<BookMark>();
		while (cur.moveToNext()) {
			BookMark b = new BookMark();
			b.setBookMarkId(cur.getInt(0));
			Log.v("debug", "queryResult:" + cur.getInt(0));
			b.setMarkName(cur.getString(1));
			b.setCurrentOffset(cur.getInt(2));
			b.setSaveTime(cur.getString(3));
			list.add(b);

		}
		cur.close();
		System.gc();
		return list;
	}

	/**
	 * 清除此书籍在数据库中的数据
	 * 
	 * @param bookName
	 * @return
	 */
	public boolean deleteBook(String bookName) {
		String tag = "deleteBook";
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
		}
		String[] col = new String[] { Constant.BOOK_ID, Constant.BOOK_PATH };
		Cursor cur = sql.query(Constant.BOOK, col, Constant.BOOK_PATH + "=\""
				+ bookName + "\"", null, null, null, null);

		Log.d(tag, "query the book info from the database....");

		int bookid = 0;
		while (cur.moveToNext()) {
			bookid = cur.getInt(0);
		}

		Log.d(tag, "delete all book mark of this book...");
		boolean result = true;
		result = clearAllBookMarkForBook(bookid);// 清除书签
		if (result) {
			// 清除书籍
			String s = "delete from " + Constant.BOOK_TABLE_NAME + " where "
					+ Constant.BOOK_ID + "=" + bookid;
			Log.d(tag, s);
			sql.execSQL(s);
		}
		cur.close();
		return result;
	}

	/**
	 * 清除某本书的全部书签
	 * 
	 * @param bookId
	 * @return
	 */
	public boolean clearAllBookMarkForBook(int bookId) {
		String tag = "clearAllBookMarkForBook";
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
			Log.d(tag, "open the database...");
		}

		Log.d(tag, "delete all book mark...");
		String s = "delete from " + Constant.BOOK_MARK_TABLE_NAME + " where "
				+ Constant.BOOK_ID + " =" + bookId;
		Log.d(tag, s);
		sql.execSQL(s);

		return true;
	}

	/** close the database */
	public void close() {
		if (isopen) {
			sql.close();
			isopen = false;
		}
	}

	// 添加笔记
	public boolean addNote(Note note) {
		try{
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
		}
		ContentValues values = new ContentValues();
		values.put(Constant.BOOK_ID, note.getBookId()+"");
		values.put(Constant.NOTENAME, note.getNoteTitle()+"");
		values.put(Constant.NOTECATALOG, note.getNoteCatalog()+"");
		values.put(Constant.NOTECONTENT, note.getNoteContent()+"");
		if(note.getNoteTime()==null){
			values.put(Constant.NOTESAVETIME, new Date().toString());
		}else
		values.put(Constant.NOTESAVETIME, note.getNoteTime()+"");
		values.put(Constant.NOTEOFFSET, note.getOffset()+"");
		long x = sql.insert(Constant.NOTE_TABLE_NAME, null, values);
		Log.v("debug", "addNote:"+Constant.NOTE_TABLE_NAME+x);
		if (x > 0) {
			return true;
		} else {
			return false;
		}
		}catch(Exception exp){
			DebugUtil.debug("debug", exp);
		}
		return false;
	}

	// 查询笔记
	public List<Note> queryAllNote(String bookId) {
		List<Note> list = new ArrayList<Note>();
		try{
		if (!isopen) {
			isopen = true;
			sql = getReadableDatabase();
		}
		String[] columns = new String[] { Constant.BOOK_ID,
				Constant.NOTECATALOG, Constant.NOTECONTENT, Constant.NOTENAME,
				Constant.NOTEOFFSET, Constant.NOTESAVETIME };
		Cursor cur = sql.query(Constant.NOTE_TABLE_NAME, columns,
				Constant.BOOK_ID + "=\"" + bookId + "\"", null, null, null,
				null);
		
		while (cur.moveToNext()) {
			Note n = new Note();
			n.setBookId(cur.getString(0));
			n.setNoteCatalog(cur.getString(1));
			n.setNoteContent(cur.getString(2));
			n.setNoteTitle(cur.getString(3));
			n.setOffset(cur.getString(4));
			n.setNoteTime(cur.getString(5));
			list.add(n);
		}
		cur.close();
		System.gc();
		}		catch(Exception exp){
			DebugUtil.debug("debug", exp);
		}
		return list;
	}

	// 删除笔记
	public void delNote(String time) {
		if (!isopen) {
			isopen = true;
			sql = getWritableDatabase();
		}
		String s = "delete from " + Constant.NOTE_TABLE_NAME + " where "
				+ Constant.NOTESAVETIME + " is '" + time+"'";
		try{
			Log.v("debug", s);
		sql.execSQL(s);
		}catch(Exception ex){
			DebugUtil.debug("debug", ex);
		}
	}
}
